<?php
# log_func.php  for ezBOO WebStats 2.0
# 5 june 2001
# 10 September 2001:
# ExtractAgentFunction has been improved
# 15 Dec 2001:
# ExtractAgentFunction has been improved again: no more timeout problem !



##################################################################################
# Archive fucntion is used to clean logtable from old data
# The log table (logezboo) should only contain current month data
# Archive fucntion will bet older (than current) months and create an history table
# with "_h" at the end to store total hist for all previous months
function Archive($table_log,$month_kept) {
 // Global variables for SQL connection
 global $server;
 global $user;
 global $password;
 global $base;

 require("config.inc.php");

 $c = @mysql_connect("$server","$user","$password") or die($msgSqlConnectImpossible);
 $ddb = @mysql_select_db("$base") or die($msgBaseConnectImpossible);

 // Get Oldest month in the table
 $result = @mysql_query ("SELECT month(min(date)) FROM $table_log");
 $row = @mysql_fetch_array ($result);

 $today_month = date("n");    // current month
 $today_year = date("Y");     // current year

 // Start archive if months are different
 if ($row[0]<=($today_month-$month_kept) and $row[0]!= NULL) {   //$row[0] is oldest month in the base
      # Oldest month in the table
      $result = @mysql_query ("SELECT month(min(date)) FROM $table_log");
      $row = @mysql_fetch_array ($result);
      $oldest_month = $row[0];    //oldest month in the log table

      # Oldest year in the table
      $result = @mysql_query ("SELECT year(min(date)) FROM $table_log");
      $row = @mysql_fetch_array ($result);
      $oldest_year = $row[0];

      while ( ($oldest_month.$oldest_year != $today_month.$today_year) ) {
              # Count hits for the oldest month
              $result = @mysql_query ("SELECT COUNT(*) FROM $table_log WHERE MONTH(date)=$oldest_month and YEAR(date)=$oldest_year");
              $row = @mysql_fetch_array ($result);
              echo $oldest_month." - ".$oldest_year." = ".$row[0]."<br><br>";
              $hits = $row[0];

              # Write in history table
              $table_log_h = $table_log."_h";
              $wdate = $oldest_year."-".$oldest_month."-01";
              $result = @mysql_query("INSERT INTO $table_log_h (id, date, hits) VALUES ('', '$wdate', '$hits')")
                 or die($msgCanotWriteArchive);


              #Clear log table
              $result = @mysql_query("DELETE FROM $table_log WHERE month(date)=$oldest_month AND year(date)=$oldest_year");

              $oldest_month = $oldest_month + 1;
              if ($oldest_month == 13) {
                $oldest_month = 1;
                $oldest_year = $oldest_year + 1;
              }
      }  # end while loop
      echo $msgArchiveCreated."\n";
 }  //end if
 @mysql_free_result ($result);
 @mysql_close ($c);        // close sql connection
} # End of function Archive

##################################################################################
# This function will output in html format a table
# with a title ($title) and a list of Items with corresponding
# bar graph representing percentage
# $val must be an array such as:
#    [Total] => 20
#    [Win95] => 12
#    [Win98] => 7
#    [WinNT] => 1
##################################################################################
function ProcessBarGraph($title, $val) {
 global $period;
 global $table;
 global $reqdate;

 global $msgCountrySort;
 global $msgBrowserSort;
 global $msgOsSort;
 global $msgProviderSort;
 global $config;

 $url_trunc_size = $config["url_trunc_size"];

 echo "<table width=\"350\" border=\"0\" cellspacing=\"0\" cellpadding=\"3\" class=\"normal\">";
 echo "<tr bgcolor=\"#CCCCCC\">";
 echo "<td colspan=\"2\"><b>".$title."</b></td>";
 echo "<td width=\"0\">"."<div align=\"center\">"."Hits"."</div></td>";
 echo "<td>"."<div align=\"center\">"."%"."</div></td>";
 echo "</tr>";
// echo "<tr  onMouseover=\"this.bgColor='#EEEEEE'\" onMouseout=\"this.bgColor='$color'\">";

 $sta = true;

 # Find max of percentage
 $temparray = $val;
 $temparray[Total] = 0;
 if ($val["Total"]>0) $maxpcbar = 100 * max($temparray) / $val["Total"];

 while (($bar=each($val)) && ($val[Total]<> 0)) {
   if ($bar[0]<>"Total") {
      if ($sta) { $color = "#CCCCFF" ; } else { $color = "#FFFFCC" ;}
      $pcbar = round(100 * $bar[1] / $val["Total"]);
      $barwidth = round(100 * $pcbar / $maxpcbar);

      echo "<tr bgcolor=\"$color\" onMouseover=\"this.bgColor='#FF9999'\" onMouseout=\"this.bgColor='$color'\">";

      # title
      if (substr($bar[0],0,4)=="http") {
//            echo "<td width=\"180\"  bgcolor=\"$color\">"."<a href=\"read.php?table=".$table."&period=".$period."&reqdate=".$reqdate."&stp=referer&stp2=".urlencode($bar[0])."\">+ </a>"."<a href=\"".$bar[0]."\" target=\"_blank\">".substr($bar[0],7,$url_trunc_size)."</a>"."</td>";
            echo "<td width=\"180\">"."<a href=\"read.php?table=".$table."&period=".$period."&reqdate=".$reqdate."&stp=referer&stp2=".urlencode($bar[0])."\"><img src=\"image/question.gif\" width=\"12\" height=\"16\" border=\"0\"></a> "."<a href=\"".$bar[0]."\" target=\"_blank\">".substr($bar[0],7,$url_trunc_size)."</a>"."</td>";
//            echo "<td width=\"180\"  bgcolor=\"$color\">"."<a href=\"".$bar[0]."\" target=\"_blank\">".substr($bar[0],7,20)."</a>"."</td>";
      } else

       if (substr($bar[0],1,4)=="http") {
            echo "<td width=\"180\">"."<a href=\"read.php?table=".$table."&period=".$period."&reqdate=".$reqdate."&stp=referer&stp2=".urlencode($bar[0])."\"><img src=\"image/question.gif\" width=\"12\" height=\"16\" border=\"0\"></a> "."<a href=\"".$bar[0]."\" target=\"_blank\">".substr($bar[0],7,$url_trunc_size)."</a>"."</td>";
      } else




      // Special link for country
      if ($title==$msgCountrySort ) {
            echo "<td width=\"180\">"."<a href=\"read.php?table=".$table."&period=".$period."&reqdate=".$reqdate."&stp=country&stp2=".$bar[0]."\">".$bar[0]."</a>"."</td>";
      } else
      // Special link for browser
      if ($title==$msgBrowserSort ) {
            echo "<td width=\"180\">"."<a href=\"read.php?table=".$table."&period=".$period."&reqdate=".$reqdate."&stp=wb&stp2=".$bar[0]."\">".$bar[0]."</a>"."</td>";
      } else
      // Special link for OS
      if ($title==$msgOsSort ) {
            echo "<td width=\"180\">"."<a href=\"read.php?table=".$table."&period=".$period."&reqdate=".$reqdate."&stp=os&stp2=".$bar[0]."\">".$bar[0]."</a>"."</td>";
      } else
      // Special link for provider
      if ($title==$msgProviderSort ) {
            echo "<td width=\"180\">"."<a href=\"read.php?table=".$table."&period=".$period."&reqdate=".$reqdate."&stp=provider&stp2=".$bar[0]."\">".$bar[0]."</a>"."</td>";
      } else
      //Regular display: no link
      echo "<td width=\"180\">".$bar[0]."</td>";

      # List of stuff
      echo "<td width=\"170\">";
      # Start of bar graph
      print("<img src=\"image/bar_1.gif\" width=\"1\" height=\"12\" alt=\"$bar[1] hits  -  $pcbar %\">");
      # Bar graph itself
      print("<img src=\"image/bar_1u.gif\" width=\"$barwidth\" height=\"12\" alt=\"$bar[1] hits  -  $pcbar %\">");
      # End of bar graph
      print("<img src=\"image/bar_1.gif\" width=\"1\" height=\"12\" alt=\"$bar[1] hits  -  $pcbar %\">");
      echo "</td>";

      echo "<td>"."<div align=\"center\">".$bar[1]."</div></td>";
      echo "<td>"."<div align=\"center\">".$pcbar."%</div></td>";

      echo "</tr>";
      $sta = !$sta;
   }
 }
    # print total
   echo "<tr bgcolor=\"#CCCCCC\">";
   echo "<td colspan=\"4\"><div align=\"right\">Total: ".$val[Total]."</div></td>";
   echo "<tr>";

 echo "</table>";
} # end of fucntion ProcessBarGraph

##################################################################################
# This function now count hits in history table and in current table
function MonthSort($table_log, $reqdate) {
 // Global variables for SQL connection
 global $server;
 global $user;
 global $password;
 global $base;

 global $msgSqlConnectImpossible;
 global $msgBaseConnectImpossible;
 global $msgMonthsArray;

 $today_array = getdate($reqdate);
 $req_month = $today_array["mon"];

 $c = @mysql_connect("$server","$user","$password") or die($msgSqlConnectImpossible);
 $ddb = @mysql_select_db("$base") or die($msgBaseConnectImpossible);

 // Query in history table
 $table_log_h = $table_log."_h";
 $val_array = array("Total"=>0);
 $result = @mysql_query ("SELECT UNIX_TIMESTAMP(date),hits FROM $table_log_h order by date ASC");
// if ($result !="") $row = @mysql_fetch_array ($result);

 if ($result!= NULL) {
    while ($row = @mysql_fetch_array ($result)) {
       $date_array = getdate($row[0]);
       $val_array = $val_array + array($msgMonthsArray[$date_array["mon"]-1]." ".$date_array["year"]=>$row[hits]);
       $val_array["Total"] = $val_array["Total"] + $row[hits];
    } # end while
 }

  $last_month_h = $date_array["mon"];
  $last_year_h = $date_array["year"];

  // echo "Newest in history table=".$last_month_h." ".$last_year_h."<br>";
  // echo "Server Date=".date(m)." ".date(Y)."<br>";

  if ($last_month_h=='') {  //historay table is empty
    $result = @mysql_query ("select UNIX_TIMESTAMP(min(date)) from $table_log");
    if ($result !="") $row = @mysql_fetch_array ($result);
    $date_array = getdate($row[0]);
    $last_month_h = $date_array["mon"];
    $last_year_h = $date_array["year"];
    $last_month_h = $last_month_h - 1;
       if ($last_month_h==0) {
           $last_year_h = $last_year_h -1;
           $last_month_h=12;
       }

  }



  do {
       $last_month_h = $last_month_h + 1;
       if ($last_month_h==13) {
           $last_year_h = $last_year_h +1;
           $last_month_h=1;
       }
       $result = @mysql_query ("select count(*) from $table_log where month(date)=$last_month_h and year(date)=$last_year_h order by date asc");
       $row = @mysql_fetch_array ($result);
       $val_array = $val_array + array($msgMonthsArray[$last_month_h-1]." ".$last_year_h=>$row[0]);
       $val_array["Total"] = $val_array["Total"] + $row[0];
   }  while ( !($last_month_h==date(n) and $last_year_h==date(Y)));


 @mysql_free_result ($result);
 @mysql_close ($c);
 return $val_array;
}

##################################################################################
function DaySort($table_log, $reqdate, $period) {
 // Global variables for SQL connection
 global $server;
 global $user;
 global $password;
 global $base;

 $c = @mysql_connect("$server","$user","$password") or die($msgSqlConnectImpossible);
 $ddb = @mysql_select_db("$base") or die($msgBaseConnectImpossible);
 # select what to search for depending on selected period
 switch ($period) {
    case "semaine":
        $q_string = "SELECT UNIX_TIMESTAMP(date) FROM $table_log WHERE WEEK(date)=WEEK(FROM_UNIXTIME('$reqdate')) order by date ASC";
        $result = @mysql_query ($q_string);
        break;
    case "mois":
        $q_string = "SELECT UNIX_TIMESTAMP(date) FROM $table_log WHERE MONTH(date)=MONTH(FROM_UNIXTIME('$reqdate')) order by date ASC";
        $result = @mysql_query ($q_string);
       break;
 }
 $val_array = array("Total"=>0);
 $last_date_array = array(0,0,0,0,0,0,0,0,0,0);
 $nb_lastval = 1;
 $today_array = getdate($reqdate);
 $i = 0;
 while ($row = @mysql_fetch_array ($result)) {
    $date_array = getdate($row[0]);
    if ($date_array["year"] == $today_array["year"]) {
       if ($date_array["mon"] == $today_array["mon"]) {
              if (  ($last_date_array["mday"] == $date_array["mday"])  ) {
                 $nb_lastval += 1;
              } else {
                         if ($i<>0) {
                           $val_array = $val_array + array($last_date_array["mday"]."/".$last_date_array["mon"]."/".$last_date_array["year"]=>$nb_lastval);
                           $nb_lastval=1;
                         }
                     }
              $last_date_array = $date_array;
              $i += 1;
       }  # end if mon
    }  # end if year
 } # end while
 $val_array = $val_array + array($last_date_array["mday"]."/".$last_date_array["mon"]."/".$last_date_array["year"]=>$nb_lastval);
 $val_array["Total"] = $i;
 @mysql_free_result ($result);
 @mysql_close ($c);
 return $val_array;
}

##################################################################################
function HourSort($table_log, $reqdate) {
 // Global variables for SQL connection
 global $server;
 global $user;
 global $password;
 global $base;

 $c = @mysql_connect("$server","$user","$password") or die($msgSqlConnectImpossible);
 $ddb = @mysql_select_db("$base") or die($msgBaseConnectImpossible);
 $result = @mysql_query ("SELECT UNIX_TIMESTAMP(date) FROM $table_log WHERE DAYOFYEAR(date)=DAYOFYEAR(FROM_UNIXTIME($reqdate)) order by date ASC");

 $val_array = array("Total"=>0);
 $last_date_array = array(0,0,0,0,0,0,0,0,0,0);
 $nb_lastval = 1;
 $today_array = getdate($reqdate);

 $i = 0;
 while ($row = @mysql_fetch_array ($result)) {
    $date_array = getdate($row[0]);
    if ($date_array["year"] == $today_array["year"]) {
       if ($date_array["mon"] == $today_array["mon"]) {
          if ($date_array["mday"] == $today_array["mday"]) {
              if (  ($last_date_array["hours"] == $date_array["hours"])  ) {
                 $nb_lastval += 1;
              } else {
                         if ($i<>0) {
                           $val_array = $val_array + array($last_date_array["hours"]." h"=>$nb_lastval);
                           $nb_lastval=1;
                         }
                     }
              $last_date_array = $date_array;
              $i += 1;
          }  # end if mday
       }  # end if mon
    }  # end if year
 } # end while

 $val_array = $val_array + array($last_date_array["hours"]." h"=>$nb_lastval);
 $val_array["Total"] = $i;
 @mysql_free_result ($result);
 @mysql_close ($c);
 return $val_array;
}

##################################################################################
# This function will class a given collumn $field
# $field must be equal to a column field (i.e.: provider)
# function output a array like for os:
#    [Total] => 20
#    [Win95] => 12
#    [Win98] => 7
#    [WinNT] => 1
##################################################################################
function ClassAndCountField($table_log, $field,$reqdate,$period) {
 // Global variables for SQL connection
 global $server;
 global $user;
 global $password;
 global $base;

 // Global variables from config.inc.php
 global $sites;

 $c = @mysql_connect("$server","$user","$password") or die($msgSqlConnectImpossible);
 $ddb = @mysql_select_db("$base") or die($msgBaseConnectImpossible);
 # select what to search for depending on selected period
 switch ($period) {
    case "jour":
        $q_string = "SELECT $field FROM $table_log WHERE DAYOFYEAR(date)=DAYOFYEAR(FROM_UNIXTIME('$reqdate')) order by $field ASC";
        $result = @mysql_query ($q_string);
        break;
    case "semaine":
        $q_string = "SELECT $field FROM $table_log WHERE WEEK(date)=WEEK(FROM_UNIXTIME('$reqdate')) order by $field ASC";
        $result = @mysql_query ($q_string);
        break;
    case "mois":
        $q_string = "SELECT $field FROM $table_log WHERE MONTH(date)=MONTH(FROM_UNIXTIME('$reqdate')) order by $field ASC";
        $result = @mysql_query ($q_string);
       break;
 }

 $val_array = array("Total"=>0);
 $lastval = "xxx";
 $i = 0;
 $nb_lastval = 1;

 if ($field !="referer") {
        while ($row = @mysql_fetch_array ($result)) {
           if ($lastval == $row[$field]) {
             $nb_lastval += 1;
           } else {
                if ($i<>0) {
                      $val_array = $val_array + array("$lastval"=>$nb_lastval);
                      $nb_lastval=1;
                }
             }
           $lastval = $row[$field];
           $i += 1;
        }
  $val_array = $val_array + array("$lastval"=>$nb_lastval);
  $val_array["Total"] = $i;
 }  else {
        while ($row = @mysql_fetch_array ($result)) {
          if (  $row[0]!='' and !eregi($sites[$table_log],$row[0])) {
             if ($lastval == $row[$field]) {
               $nb_lastval += 1;
             } else {
                  if ($i<>0) {
                        $val_array = $val_array + array("$lastval"=>$nb_lastval);
                        $nb_lastval=1;
                  }
               }
             $lastval = $row[$field];
             $i += 1;
          }
        }

  if (!eregi($sites[$table_log],$lastval) and $lastval!="" ) {
     $val_array = $val_array + array("$lastval"=>$nb_lastval);
     $val_array["Total"] = $i;
  }
 }


 arsort($val_array);
 @mysql_free_result ($result);
 @mysql_close ($c);
 return $val_array;
}
##################################################################################
# This function will attempt to fill empty field in table_log such as
# country, provider, os, wb
##################################################################################
function ProcessEmptyEntry($table_log) {
 // Global variables for SQL connection
 global $server;
 global $user;
 global $password;
 global $base;

    // Read the os_list and assign 2 arrays (label and id)
    global $config;
    $buffer = split ("\n",$config[os_list]);
    $i=0;
    foreach($buffer as $buffer1) {
       list ($os_id[$i], $os_label[$i]) = split ('[|]', $buffer1);
       $i+=1;
    }

    // Read the browser_list and assign 2 arrays (label and id)
    $buffer = split ("\n",$config[browser_list]);
    $i=0;
    foreach($buffer as $buffer1) {
       list ($browser_id[$i], $browser_label[$i]) = split ('[|]', $buffer1);
       $i+=1;
    }



 $c = @mysql_connect("$server","$user","$password") or die($msgSqlConnectImpossible);
 $ddb = @mysql_select_db("$base") or die($msgBaseConnectImpossible);
 # Fill country column
 $result = @mysql_query ("SELECT * from $table_log WHERE country='' ");
 while ($row = @mysql_fetch_array ($result)) {
    $newval=ExtractCountry($row[host]);
    $req = @mysql_query("UPDATE $table_log SET country='$newval' where id='$row[id]' ");
 }
 # Fill provider column
 $result = @mysql_query ("SELECT * from $table_log WHERE provider='' ");
 while ($row = @mysql_fetch_array ($result)) {
    $newval=ExtractProvider($row[host],$row[address]);
    $req = @mysql_query("UPDATE $table_log SET provider='$newval' where id='$row[id]' ");
 }
 # Fill os column
 $result = @mysql_query ("SELECT * from $table_log WHERE os='' ");
 while ($row = @mysql_fetch_array ($result)) {
    list($wb,$newval)=split(";",ExtractAgent($row[agent],$browser_id,$browser_label,$os_id,$os_label));
    $req = @mysql_query("UPDATE $table_log SET os='$newval' where id='$row[id]' ");
 }

 # Fill wb column
 $result = @mysql_query ("SELECT * from $table_log WHERE wb='' ");
 while ($row = @mysql_fetch_array ($result)) {
    list($newval,$os)=split(";",ExtractAgent($row[agent],$browser_id,$browser_label,$os_id,$os_label));
    $req = @mysql_query("UPDATE $table_log SET wb='$newval' where id='$row[id]' ");
 }
 @mysql_free_result ($result);
 # @mysql_free_result ($req);
 @mysql_close ($c);
 return True;
} # end of function ProcessEmptyEntry

##################################################################################
# This function will clear : country, provider, os, wb
##################################################################################
function ClearAll($table_log) {
 // Global variables for SQL connection
 global $server;
 global $user;
 global $password;
 global $base;

 global $table;
 global $reqdate;
 global $period;
 global $msgContinue;

 $c = @mysql_connect("$server","$user","$password") or die($msgSqlConnectImpossible);
 $ddb = @mysql_select_db("$base") or die($msgBaseConnectImpossible);
 $req = @mysql_query("UPDATE $table_log SET country=''");
 $req = @mysql_query("UPDATE $table_log SET provider=''");
 $req = @mysql_query("UPDATE $table_log SET os=''");
 $req = @mysql_query("UPDATE $table_log SET wb=''");
 # @mysql_free_result ($req);
 @mysql_close ($c);

 echo "<a href=\"".basename($PHP_SELF)."?table=$table&reqdate=$reqdate&reset=0&period=$period"."\">$msgContinue</a>";
 echo "<br>";
 exit;

 return True;
}  # end of ClearAll


##################################################################################
# Use this function to extract domain name from a Host name
#  $hst should be equal to @getHostByAddr($REMOTE_ADDR)
#  for exemple www.ezboo.fr will return ezboo.fr
# but www.ezboo.co.jp will return ezboo.co.jp
##################################################################################
function ExtractProvider($hst, $addressip) {
 global $msgOther;

 $hst_array = split("[.]",$hst);
 $prov = $hst_array[sizeof($hst_array)-2].'.'.$hst_array[sizeof($hst_array)-1];
 if ($hst != $addressip) {
   if ($prov == "co.jp" or $prov == "co.uk" ) return $hst_array[sizeof($hst_array)-3].".".$prov;
     else return $prov ;
 } else return $msgOther;
}  #end of ExtractProvider

##################################################################################
# Use this function to extract country name from a Host name
#  $hst should be equal to @getHostByAddr($REMOTE_ADDR)
#  for exemple www.ezboo.fr will return France
##################################################################################
function ExtractCountry($hst) {
 // Global variables for SQL connection
 global $server;
 global $user;
 global $password;
 global $base;

 global $msgSqlConnectImpossible;
 global $msgBaseConnectImpossible;
 global $msgSqlQuerryError;
 global $msgUnknown;

 $hst_array = split("[.]",$hst);
 $lastval = $hst_array[sizeof($hst_array)-1];  # last value in host name
 $table_dom = "liste_domaines";
 $c2 = @mysql_connect("$server","$user","$password") or die($msgSqlConnectImpossible);
 $db2 = @mysql_select_db("$base") or die($msgBaseConnectImpossible);
 $req2 = @mysql_query("select description from $table_dom where domaine='$lastval' ") or die ($msgSqlQuerryError);

 # Check that extension exist and return country name
 # Otherwise return Unknown
 if (@mysql_numrows($req2) > 0)
   return @mysql_result($req2,0);
   else return $msgUnknown;
 @mysql_close ($c2);
}  # end of  ExtractCountry



##################################################################################
### Fonction de correction2 du nom de l'agent (navigateur et OS)
### Output syntax is a string like WebBrowser;OS
### You can extract OS and WebBrowser using:
### list($wb,$os)=split(";",ExtractAgent($HTTP_USER_AGENT));
### echo $wb."<br>";
### echo $os."<br>";
##################################################################################
function ExtractAgent($agt,$browser_id,$browser_label,$os_id,$os_label) {

  // Init default values
  $new_agt_browser="???";   // Do not change these ??? . It is used in other scripts
  $new_agt_os="???";        // Do not change these ??? . It is used in other scripts
  // Check for browser
  for ($cpt = 0; $cpt < count($browser_id) ; $cpt++) {
     if (ereg($browser_id[$cpt],$agt)) $new_agt_browser=$browser_label[$cpt];
  }
  // Check for OS
  for ($cpt = 0; $cpt < count($os_id) ; $cpt++) {
     if (ereg($os_id[$cpt],$agt)) $new_agt_os=$os_label[$cpt];
  }


  return($new_agt_browser.";".$new_agt_os);   # Systax is=  WebBrowser;OS
}   # End of function ExtraireAgent
##################################################################################
?>